using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using webapp4U.BOL;

namespace webapp4U.DAL
{
	public class UserDAL: BaseDAL
    {

        #region ObjIDataReader

        /// <summary>
        /// Returns a new User instance filled with the DataReader's current record data 
        /// </summary>
        private User GetUserFromReader(IDataReader dr)
        {
            User obj = new User();
            obj.UserID = (dr["UserID"] is DBNull) ? String.Empty : Convert.ToString(dr["UserID"]);
            obj.UserName = (dr["UserName"] is DBNull) ? String.Empty : (String)dr["UserName"];
            obj.Password = (dr["Password"] is DBNull) ? String.Empty : (String)dr["Password"];
            obj.FirstName = (dr["FirstName"] is DBNull) ? String.Empty : (String)dr["FirstName"];
            obj.LastName = (dr["LastName"] is DBNull) ? String.Empty : (String)dr["LastName"];
            obj.Gender = (dr["Gender"] is DBNull) ? String.Empty : (String)dr["Gender"];
            obj.BirthDay = (dr["BirthDay"] is DBNull) ? String.Empty : (String)dr["BirthDay"];
            obj.Address = (dr["Address"] is DBNull) ? String.Empty : (String)dr["Address"];
            obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String)dr["Email"];
            obj.Phone = (dr["Phone"] is DBNull) ? String.Empty : (String)dr["Phone"];
            obj.Mobile = (dr["Mobile"] is DBNull) ? String.Empty : (String)dr["Mobile"];
            obj.Fax = (dr["Fax"] is DBNull) ? String.Empty : (String)dr["Fax"];
            obj.ProvinceID = (dr["ProvinceID"] is DBNull) ? Int32.MinValue : (Int32)dr["ProvinceID"];
            obj.CountryID = (dr["CountryID"] is DBNull) ? Int32.MinValue : (Int32)dr["CountryID"];
            obj.PostalCode = (dr["PostalCode"] is DBNull) ? String.Empty : (String)dr["PostalCode"];
            obj.Company = (dr["Company"] is DBNull) ? String.Empty : (String)dr["Company"];
            obj.CompanyDescription = (dr["CompanyDescription"] is DBNull) ? String.Empty : (String)dr["CompanyDescription"];
            obj.EmployeesSize = (dr["EmployeesSize"] is DBNull) ? String.Empty : (String)dr["EmployeesSize"];
            obj.Website = (dr["Website"] is DBNull) ? String.Empty : (String)dr["Website"];
            obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? String.Empty : (String)dr["ImageUrl"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["AddedDate"];
            obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty : (String)dr["AddedBy"];
            obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean)dr["IsActive"];
            return obj;
        }
        #endregion

        /// <summary>
        /// Returns a collection of User with the data read from the input DataReader
        /// </summary>
        private List<User> GetUserCollectionFromReader(IDataReader dr)
        {
            List<User> obj = new List<User>();
            while (dr.Read())
                obj.Add(GetUserFromReader(dr));
            return obj;
        }


        // cap nhat thong tin xem User co muon nhan NewsLetter hay ko
        public Boolean UpdateNewsLetter(String Email, Boolean NewsLetter)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_UpdateNewsLetter", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
                myCommand.Parameters.Add("@NewsLetter", SqlDbType.Bit).Value = NewsLetter;
                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }


        public Boolean CheckUserName(String UserName)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    SqlCommand myCommand = new SqlCommand("Job_User_CheckUserName", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
                    string value = (String)myCommand.ExecuteScalar();
                    if (value.Trim().Length > 2)
                        return true;
                    return false;
                }
                catch
                {
                    return false;
                }
            }
        }



        //Job_User_SearchJobSeeker
        public List<User> SearchJobSeekers(String Keyword)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_SearchJobSeeker", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = Keyword;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }

        //Job_User_SearchJobSeeker
        public List<User> SearchDetailJobSeekers(String Keyword, int JobTypeID, int ExperienceLevelID, int EducationLevelID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                string strsql = "";
                strsql = strsql + "Select Job_User.*, aspnet_Roles.LoweredRoleName ";
                strsql = strsql + "From aspnet_Roles, aspnet_UsersInRoles, Job_User, Job_Resume ";
                strsql = strsql + "Where Job_User.UserID = aspnet_UsersInRoles.UserID ";
                strsql = strsql + "and aspnet_Roles.RoleID = aspnet_UsersInRoles.RoleID ";
                strsql = strsql + "and aspnet_Roles.LoweredRoleName = 'jobseekers' ";
                strsql = strsql + "and Job_User.UserID = Job_Resume.UserID ";

                if (Keyword.Length > 0)
                {
                    strsql = strsql + "And (username Like '%' + @Keyword + '%' ";
                    strsql = strsql + "OR Job_Resume.Title Like '%' + @Keyword + '%' ";
                    strsql = strsql + "OR Job_Resume.Content Like '%' + @Keyword + '%') ";
                }

                if (JobTypeID > 0)
                    strsql = strsql + "And Job_Resume.JobTypeID = " + JobTypeID;

                if (ExperienceLevelID > 0)
                    strsql = strsql + "And Job_Resume.ExperienceLevelID = " + ExperienceLevelID;

                if (EducationLevelID > 0)
                    strsql = strsql + "And Job_Resume.EducationLevelID = " + EducationLevelID;

                SqlCommand myCommand = new SqlCommand(strsql, myConn);
                myCommand.CommandType = CommandType.Text;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }

        public int Insert(User obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = obj.UserName;
                myCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = obj.Password;
                myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
                myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
                myCommand.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = obj.Gender;
                myCommand.Parameters.Add("@BirthDay", SqlDbType.VarChar).Value = obj.BirthDay;
                myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
                myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
                myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
                myCommand.Parameters.Add("@ProvinceID", SqlDbType.Int).Value = obj.ProvinceID;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                myCommand.Parameters.Add("@PostalCode", SqlDbType.VarChar).Value = obj.PostalCode;
                myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;
                myCommand.Parameters.Add("@CompanyDescription", SqlDbType.NVarChar).Value = obj.CompanyDescription;
                myCommand.Parameters.Add("@EmployeesSize", SqlDbType.NVarChar).Value = obj.EmployeesSize;
                myCommand.Parameters.Add("@Website", SqlDbType.VarChar).Value = obj.Website;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                return (int)myCommand.ExecuteNonQuery();
            }
        }

        public bool Update(User obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = obj.UserName;
                myCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = obj.Password;
                myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
                myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
                myCommand.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = obj.Gender;
                myCommand.Parameters.Add("@BirthDay", SqlDbType.VarChar).Value = obj.BirthDay;
                myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
                myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
                myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
                myCommand.Parameters.Add("@ProvinceID", SqlDbType.Int).Value = obj.ProvinceID;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                myCommand.Parameters.Add("@PostalCode", SqlDbType.VarChar).Value = obj.PostalCode;
                myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;
                myCommand.Parameters.Add("@CompanyDescription", SqlDbType.NVarChar).Value = obj.CompanyDescription;
                myCommand.Parameters.Add("@EmployeesSize", SqlDbType.NVarChar).Value = obj.EmployeesSize;
                myCommand.Parameters.Add("@Website", SqlDbType.VarChar).Value = obj.Website;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(String UserID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the Job_User with the specified ID
        /// </summary>
        public User GetUserByID(String UserID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetUserByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetUserFromReader(dr) : null);
            }
        }

        //Get All User
        public List<User> GetAllUser(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetAllUser", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  User
        public int GetCountUser()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetCountUser", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int)myCommand.ExecuteScalar();
            }
        }

        
        public String GetUserIDByUserName(String UserName)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetUserIDByUserName", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = UserName;
                return Convert.ToString(myCommand.ExecuteScalar());
            }
        }

        public String GetUserNameByUserID(String UserID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetUserNameByUserID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;
                return (String)myCommand.ExecuteScalar();
            }
        }


        public Boolean Approved(String Email)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_Approved", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email;
                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        
        //Get User By Role
        public List<User> GetUserByRole(String RoleName)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_GetUserByRole", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RoleName", SqlDbType.NVarChar).Value = RoleName;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }


        public List<User> Search(String Keyword, String RoleName, String SearchBy)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_Search", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = Keyword;
                myCommand.Parameters.Add("@LoweredRoleName", SqlDbType.NVarChar).Value = RoleName.ToLower();
                myCommand.Parameters.Add("@SearchBy", SqlDbType.VarChar).Value = SearchBy;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }

        // phan nay chi dung search cho Admin
        public List<User> AdminSearch(String Keyword, String RoleName)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_AdminSearch", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = Keyword;
                myCommand.Parameters.Add("@LoweredRoleName", SqlDbType.NVarChar).Value = RoleName.ToLower();
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetUserCollectionFromReader(dr);
            }
        }


        public List<User> Test(out int Total)
        {
            Total = 0;
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("sp_Test", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = 10;
                SqlDataReader dr = myCommand.ExecuteReader();
                Total = (int)myCommand.Parameters["@OrderID"].Value;
                return GetUserCollectionFromReader(dr);
            }
        }



        // Update ImageUrl
        public Boolean UpdateImageUrl(String ImageUrl, String UserID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_User_UpdateImageUrl", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.VarChar).Value = ImageUrl;
                myCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = UserID;
                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }



	}
}
